CREATE OR REPLACE
procedure info_pro_Brok(
  roleId in number,
  roleType in number,
  orgId in number,
  transMoney in number,
  fee in number,
  feeRate in number,
  v_orderNum in varchar2,
  clientType in number,
  orderDate in TIMESTAMP,
  longcdate in number,
  status out number,
  revenueFee in number,
  userid in number
)
as
  v_pOrgId info_organization.id%TYPE := orgId;--父机构ID
  v_clearRate NUMBER(6,5);--机构结算费率 分费率版  封顶版   pos 费率版还分公司 个人
  v_ownBrokerageRate NUMBER(6,5);--机构结算费率 分费率版  封顶版
  v_orgfdMaxFee NUMBER(10,2);--封顶最大值
  v_orgfdMinFee NUMBER(10,2);--封顶最小值
  v_brokerage NUMBER(10,2);--佣金 （自己得到的）
  v_totalBrokerage NUMBER(10,2);--自己产生的佣金
  v_temp_son_brokerage NUMBER(10,2) := 0.00;--子集佣金 默认子集没有佣金
  v_temp_org_fee NUMBER(10,2);--机构手续费

  type_record info_organization_cleaning%rowtype;--使用记录类型
  v_maori NUMBER(10,2);--毛利
  rowt_org info_organization%rowtype;--机构对象

  v_temp_son_totalBrokerage NUMBER(10,2) := 0.00;
  begin
    while (v_pOrgId > 1) loop
      select * into type_record from info_organization_cleaning oc where oc.organizationid = v_pOrgId;
      select * into rowt_org from info_organization o where o.id = v_pOrgId; --
      IF rowt_org.isDir = 1 THEN
        continue;--如果是目录那么不算佣金
      END IF;

      IF roleId = 2 THEN
        IF clientType = 2 THEN --手机
          v_clearRate := type_record.clearrate;
          v_ownBrokerageRate := type_record.ownbrokeragerate;
        ELSIF clientType = 5 THEN --pos
          v_clearRate := type_record.upclearrate;
          v_ownBrokerageRate := type_record.upownbrate;
          IF roleType = 2 THEN --pos公司
            v_clearRate := type_record.upgsclearrate;
          END IF;
        ELSIF clientType = 7 THEN --支付宝
          v_clearRate := type_record.upclearrate;
          v_ownBrokerageRate := type_record.upownbrate;
        END IF;
        v_temp_org_fee := transMoney * v_clearRate;
      ELSIF roleId = 3 THEN
        IF clientType = 2 THEN --手机
          v_clearRate := type_record.fdclearrate;
          v_ownBrokerageRate := type_record.fdownbrokeragerate;
          v_orgfdMaxFee := type_record.fdmaxfee;
          v_orgfdMinFee := type_record.fdminfee;
        ELSIF clientType = 5 THEN --pos
          v_clearRate := type_record.upfdclearrate;
          v_ownBrokerageRate := type_record.upfdownbrate;
          v_orgfdMaxFee := type_record.upfdmaxfee;
          v_orgfdMinFee := type_record.upfdminfee;
        END IF;
        v_temp_org_fee := transMoney * v_clearRate;
        IF v_temp_org_fee < v_orgfdMinFee THEN --如果封顶手续费小于最小 那么等于最小
          v_temp_org_fee := v_orgfdMinFee;
        END IF;
        IF v_temp_org_fee > v_orgfdMaxFee THEN --如果封顶手续费大于最大 那么等于最大
          v_temp_org_fee := v_orgfdMaxFee;
        END IF;
      END IF;

      v_totalBrokerage := fee - v_temp_org_fee;
      IF v_totalBrokerage > 0 THEN
        --佣金为正数 要乘以 分拥比例
        v_totalBrokerage := v_totalBrokerage * v_ownBrokerageRate;
      END IF;

      IF v_totalBrokerage = 0 THEN
        --v_temp_son_totalBrokerage := v_totalBrokerage;
        --ELSE --如果总佣金 为0 那么需要  等于下属总佣金
        v_totalBrokerage := v_temp_son_totalBrokerage;
      END IF;

      v_brokerage := v_totalBrokerage;
      --如果下级佣金是不是负数
      IF v_temp_son_brokerage > 0 THEN
        v_brokerage := v_totalBrokerage - v_temp_son_brokerage;
      END IF;

      --如果分佣比例0 那么佣金为0
      IF v_ownBrokerageRate = 0 THEN
        v_brokerage := 0;
      END IF;

      v_temp_son_totalBrokerage := v_temp_son_totalBrokerage+v_brokerage;

      --插入佣金记录
      insert into info_brok_eachorder(id,organizationid,ordernum,transmoney,fee,brokerage,totalbrokerage,feerate,
                                      clearrate,ownbrokeragerate,orgfdmaxfee,orgfdminfee,createdate,longcdate,roleid,clienttype,userid)
      values(sqc_info_brok_eachorder.nextval,v_pOrgId,v_orderNum,transMoney,fee,v_brokerage,v_totalBrokerage,feeRate,
                                             v_clearRate,v_ownBrokerageRate,v_orgfdMaxFee,v_orgfdMinFee,orderDate,longcdate,roleId,clientType,userid);

      v_temp_son_brokerage := v_totalBrokerage;--赋值子集
      select o.parentid into v_pOrgId from info_organization o where o.id = v_pOrgId; --重新赋值
    end loop;

    --修改订单
    v_maori := fee - revenueFee - abs(v_totalBrokerage);
    IF clientType = 2 THEN --手机
      update info_trans_order o set o.maori = v_maori where o.ordernum=v_orderNum;
    ELSIF clientType = 5 THEN --pos
      update up_trans ut set ut.maori = v_maori*100 where ut.tranguid=v_orderNum;
    ELSIF clientType = 7 THEN --支付宝
      update info_trans_order o set o.maori = v_maori where o.ordernum=v_orderNum;
    END IF;
    commit;
    status:=1;
    DBMS_OUTPUT.put_line('存储过程执行完成');

    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.put_line('NO_DATA_FOUND');
    status:=0;
  end info_pro_Brok;